#!/bin/bash
# Database Setup Script for YouKe Tutoring Platform
# This script sets up the database, user and tables using the provided root user credentials

echo "Setting up database for YouKe Tutoring Platform..."
echo "Using root user to create database and user..."

# Database connection parameters for root user
ROOT_HOST=localhost
ROOT_PORT=3306
ROOT_USER=root
ROOT_PASS=abcd1234

# Check if MySQL is accessible with root user
echo "Attempting to connect to MySQL as root user..."
mysql -h $ROOT_HOST -P $ROOT_PORT -u $ROOT_USER -p$ROOT_PASS -e "SELECT 1;"

if [ $? -ne 0 ]; then
    echo "✗ Cannot connect to MySQL as root user!"
    echo "Please ensure MySQL server is running and root password is correct."
    exit 1
fi

echo "✓ Root user connection successful!"

# Execute database initialization script
echo ""
echo "Creating database, user and tables..."
mysql -h $ROOT_HOST -P $ROOT_PORT -u $ROOT_USER -p$ROOT_PASS < database_init.sql

if [ $? -eq 0 ]; then
    echo "✓ Database, user and tables created successfully!"
else
    echo "✗ Error occurred while creating database and tables."
    exit 1
fi

# Execute initial data script
echo ""
echo "Inserting initial data..."
mysql -h $ROOT_HOST -P $ROOT_PORT -u $ROOT_USER -p$ROOT_PASS < init_data.sql

if [ $? -eq 0 ]; then
    echo "✓ Initial data inserted successfully!"
else
    echo "✗ Error occurred while inserting initial data."
    exit 1
fi

echo ""
echo "Database setup complete!"
echo "Created database: youke"
echo "Created user: youke (password: YouKe_12!@)"
echo "Created tables and inserted initial data."

# Test the new database user connection
echo ""
echo "Testing new database user connection..."
sh test_db_connection.sh